跳到主要内容

MySQL 学习（9）表空间、区、页

大部分转载自：MySQL(InnoDB剖析):---table之（表空间：段(segment)、区(extent)、页(page)） 本文只是对小部分内容进行了补充

索引组织表的概念​

在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在 InnoDB 存储引擎表中，每张表都有个主键（Primary Key）。因此 数据即索引，索引即数据。

如果在创建表时没有显式地定义主键，则 InnoDB 存储引擎会按如下方式选择或创建主键：

  • 首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。
  • 如果不符合上述条件，InnoDB 存储引擎自动创建一个 6 字节大小的指针。

当表中有多个非空唯一索引时， InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

InnoDB 逻辑存储结构​

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。

表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block）

InnoDB 存储引擎的逻辑存储结构大致如图所示。

表空间 tablespace​

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。

默认情况下 InnoDB 有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内，它会初始化大小为 12MB，并且随着数据的增多，它会自动扩容。

show variables like 'innodb_data_file_path';

Value 部分的的组成是：name:size:attributes

这个 ibdata1 文件是系统表空间，也是默认的表空间，也是默认的表空间物理文件。

关于这个共享表空间，直观上看，如果这个表空间能为 multiple tables（多表）存储数据，那么它就可以被称为共享表空间，所以可以认为系统表空间是共享表空间。

MySQL 有很多种表空间，下面一起来了解一下。

file_per_table 参数​

如果你想让每一个数据库表都有一个单独的表空间文件的话，可以通过参数 innodb_file_per_table 设置。

这个参数只有在 MySQL5.6 或者是更高的版本中才可以使用。

可以通过配置文件

[mysqld]
innodb_file_per_table=ON

也可以通过命令

mysql> SET GLOBAL innodb_file_per_table=ON;

让你将其设置为 ON，那之后 InnoDB 存储引擎产生的表都会自己独立的表空间文件。

独立的表空间文件命名规则：tableName.ibd

查看我的表空间文件：

如果启用了 innodb_file_per_table 参数，需要注意的是每张表的表空间内存放的只是数据、索引和插人缓冲 Bitmap 页，其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

这同时也说明了另一个问题：即使在启用了参数 innodb_file_per_table 之后，共享表空间还是会不断地增加其大小。

file_per_table 参数的优缺点​

优点：

  • 提升容错率，表A的表空间损坏后，其他表空间不会收到影响。
  • 使用 MySQL Enterprise Backup 快速备份或还原在每表文件表空间中创建的表，不会中断其他 InnoDB 表的使用

缺点：

  • 对 fsync 系统调用来说不友好，如果使用一个表空间文件的话单次系统调用可以完成数据的落盘，但是如果你将表空间文件拆分成多个。原来的一次 fsync 可能会就变成针对涉及到的所有表空间文件分别执行一次 fsync，增加 fsync 的次数。

补充：fsync系统调用：需要你在入参的位置上传递给他一个 fd，然后系统调用就会对这个 fd 指向的文件起作用。fsync 会确保一直到写磁盘操作结束才会返回，所以当你的程序使用这个函数并且它成功返回时，就说明数据肯定已经安全的落盘了。所以 fsync 适合数据库这种程序。

表空间的大小和数量​

系统表空间的数量和大小可以通过启动参数：innodb_data_file_path

# my.cnf
[mysqld]
innodb_data_file_path=/dir1/ibdata1:2000M;/dir2/ibdata2:2000M:autoextend

ibdata1 表空间为固定大小 2000M，而 ibdata2 的 2000M 使用完后会自动增长。

假设你的服务器有两块存储 A、B。并且A、B上分别挂载着 dir1 目录和 dir2 目录。那你再看上面的配置，它其实是在使用两个不同磁盘上的文件共同构建表空间。

由于这两个文件位于不同的磁盘上，磁盘的负载就会被均分，数据库整体的性能也有所提升

共享表空间的大小问题​

上面通过 innodb_file_per_table 参数我们知道即使为每个表都是用单独的表空间，但是表的有些信息还是要存放到共享表空间内，因此共享表的空间会不断增加其大小

下面进行一个演示案例，来观察共享表空间大小的增长过程

先将 innodb_file_per_table 参数设置为 “NO”。然后观察一下默认的共享表空间文件的大小（为58M）

接下来模拟产生 undo 的操作，set autocommit 设置为 0 代表用户需要显式提交事务（下图中，在事务提交结束时并没有以对钙食物执行 commit 或 rollback，因此会产生大量 undo 操作的 update 语句）。可以看到共享表空间的大小增加了

如果对事务进行回滚操作，那么共享表空间的大小会不会缩减至原来的大小呢？见下图：

答案是否，共享表空间的大小没有减小。虽然 InnoDB 不回回收这些空间，但是会自动判断这些 undo 信息是否还需要，如果不需要，则会将这些空间标记为可用空间，供下次 undo 使用

当用户再次执行上述的 update 语句后，会发现共享表空间文件不会再增大了，因为其会利用之前的 undo 信息

拓展：sys 表空间​

你可以像下面这样查看你的 MySQL 的系统表空间

默认情况下，MySQL会初始化一个大小为12MB，名为 ibdata1 文件，并且随着数据的增多，它会自动扩容。

这个 ibdata1 文件是系统表空间，也是默认的表空间，也是默认的表空间物理文件，也是传说中的共享表空间。

关于这个共享表空间，直观上看，如果这个表空间能为 multiple tables（多表）存储数据，那么它就可以被称为共享表空间，所以你可以认为系统表空间是共享表空间。

拓展：临时表空间​

临时表空间用于存放用户创建的临时表和磁盘内部临时表。

参数 innodb_temp_data_file_path 定义了临时表空间的一些名称、大小、规格属性如下图：

查看临时表空间文件存放的目录

拓展：undo 表空间 ⭐​

相信你肯定听过说 undolog，常见的当你的程序想要将事物 rollback 时，底层 MySQL其实就是通过这些undo信息帮你回滚的。

在 MySQL 的设定中，有一个表空间可以专门用来存放 undolog 的日志文件。然而，MySQL 默认的会将 undolog 放置到系统表空间中。

如果你的 MySQL 是新安装的，那你可以通过下面的命令看看你的 MySQL undo 表空间的使用情况：

大家可以看到 MySQL 的 undo log 表空间有两个。

也就是我的 undo 从默认的系统表空间中转移到了 undo log 专属表空间中了。

那 undo log 到底是该使用默认的配置放在系统表空间呢？还是该放在 undo 表空间呢？

这其实取决服务器使用的存储卷的类型。

如果是 SSD 存储，那推荐将 undo info 存放在 undo 表空间中。

Python 脚本：查看表空间中各页的信息​

原作者提供了用于查看页信息的 Python 脚本，此脚本可以在GitHub进行下载：https://github.com/jameslcj/david-mysql-tools

这个脚本用来查看表空间中各页的类型和信息，使用方法如下：

它表示：共有 83584 个页。其中插入缓冲的空间列表有204个页、5467个可用页、38675个undo页、39233个数据页等

用户可以通过 -v 选项来查看更详细的内容

段 segment​

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。

因为前面已经介绍过了 InnoDB 存储引擎表是索引组织的（index organized），因此 数据即索引，索引即数据。

那么数据段即为 B+ 树的叶子节点（如这图的 Leaf Node Segment）

索引段即为 B+ 树的非索引节点（图上的 Non-leaf node segment）。

在 InnoDB 存储引擎中，对段的管理都是由引擎自身所完成，DBA 不能也没有必要对其进行控制。

区 extent​

区是由连续页组成的空间，在任何情况下 每个区的大小都为 1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请 4~5 个区

区中页的数量​

在默认情况下，InnoDB 存储引擎 页的大小为 16KB，即一个区一共有 64 个连续的页，不过 InnoDB 1.0.x 开始引入压缩页，即每个页的大小可以通过参数 KEY_BLOCK_SIZE 设置为 2K、4K、8K，因此每个区对应页的数量就应该为 512、256、128

InnoDB 1.2.x 开始新增了参数 innodb_page_size，通过该参数可以将默认页的大小设置为 4K、8K。但是页中的数据库不是压缩。这时区中页的数量同样也为 256、128

show variables like 'innodb_page_size';

默认是 16K

总之，不论页的大小怎么变化，区的大小总是为 1M

区的申请方式（碎片页）​

当启用参数 innodb_file_per_table 后，创建的表默认大小是 96KB。但是我们知道一个区就占用了 1MB，那么为什么只有 96KB呢？

这是因为在每个段开始时，先用 32 个页大小的碎片页去存放数据，在使用完这些页之后才开始申请 64 个连续的页 这样做的目的是：对于一些小表，或者是 undo 这类的段，可以在开始时申请较少的空间，节省磁盘容量的开销

区申请的演示案例​

下面通过一个很小的示例来显示 InnoDB 对于区的申请方式（就是在用完 32 个页之后才申请区）

第一步：创建一个 t1 表，将 col2 字段设为 VARCHAR(7000)，这样就能保证一个页最多可以存放 2 条记录（因为一个页的大小为 16KB * 1024 = 16384bit ）。然后通过 ls 命令查看到表空间默认大小为 96KB

第二步：接着向表中插入两条 SQL 语句，然后查看表空间的大小发现空间没有增加，并且这两条记录应该位于同一个页中

第三步：此时再去使用上面的工具查看表空间，可以看到

  • page offset 为 3 的页：这个就是数据页
  • page level：表示所在索引层，0 表示叶子节点。因为当前所有记录都在一个页中，因此没有非叶子节点

第四步：现在我们再插入一条记录，就会产生一个非叶节点：

page level：page offset 为 3 的页的 page level 由之前的 0 变为了 1，这时虽然新插入的记录导致 B+ 树的分裂操作，但这个页的类型还是 B-tree Node

第五步：接着上述同样的操作，再插入 60 条记录，也就是说当前表 t1 中共有 63 条记录，32 个页。为了导入的方便，在这之前先建立一个导入的存储过程（让它自动插入）

可以看到，在导入了 63 条数据后，表空间的大小还是小于 1MB，即表示数据空间的申请还是通过碎片页，而不是通过 64 个连续页的区

第六步：再次查看表空间文件，可以观察到 B-tree Node 一共有 33 个，除去一个 page level 为 1 的非叶节点页，一共有 32 个 page level 为 0 的页，也就是说，对于数据库，已经有 32 个碎片页了。之后用户再申请空间，则表空间按连续 64 个页的大小开始增长了

第七步：现在继续插入一条记录，之后看看表空间的大小。因为已经用完了 32 个碎片页，新的页回采用区的方式进行空间的申请

第八步：现在再次分析表空间文件

页 page​

页有时候也称为块

页是 InnoDB 磁盘管理的最小单位

innodb_page_size 参数​

在 InnoDB 存储引擎中，默认每个页的大小为 16KB

而从 InnoDB 1.2.x 开始，可以通过 innodb_page_size 将页的大小设置为 4K、8K、16K。若设置完成，则所有页的大小都为 innodb_page_size，不可以对其再次进行修改。

除非通过 mysqldump 导入和导出操作来产生新的库

常见的页类型​

  • 数据页（B- tree Node）
  • undo页（undo Log Page）
  • 系统页（System Page）
  • 事务数据页（Transaction system Page）
  • 插入缓冲位图页（Insert Buffer Bitmap）
  • 插入缓冲空闲列表页（Insert Buffer Free List）
  • 未压缩的二进制大对象页（Uncompressed BLOB Page）
  • 压缩的二进制大对象页（compressed BLOB Page）